Define Search Tab

The Define Search tab is divided into two panes. The left pane (search builder area) lets you define the search criteria for a report using eQL. The right pane provides hints to assist you in building the search criteria. The Textual and Graphical tabs present the eQL search criteria in two presentation methods. Typically the textual presentation is used unless you are defining a very complex multi-level search.

An eQL query consists of four parts:

The START WITH clause specifies the primary object
The SELECT clause specifies the columns to return
The WHERE clause specifies the filtering criteria
The ORDER BY clause specifies the sort order (optional)

START WITH Clause

When you begin to type the Start With clause and other search criteria, a pop-up menu opens to suggest eQL elements. If the element you are typing is highlighted, you can double-click the mouse to add that element to the search builder area.

SELECT Clause

After you enter the primary object for the START WITH clause, you can type SELECT and press <Enter> to specify the columns to return. You must specify a comma at the end of each item in the SELECT clause, except the last item.

Other helpful buttons are available in the right pane. The Fields button lists the eQL elements available for the remaining clauses. You can navigate down the Fields tree and double-click on an eQL element to add it to the search builder area.

When you click an eQL element, the Quick Info button may provide detailed information applicable to the element. The image below illustrates that the Quick Info button provides the Code (internal id) and Name (user interface word) for the eQL element Code.

The Functions button provides information on the available system functions that you can use to manipulate data defined in the search builder area. For example, the DISPLAY_NAME function may be used in conjunction with the Document eQL element ApprovalStatus to allow user-friendly wording (Planned, Not Approved, Approved) to be returned in the report results instead of the raw data (P, N, A).

Note: Since the column names that eQL uses internally may not be the names you want to see in your report output, you can specify an alias for the column name. In the above example, the column DISPLAY_NAME (ApprovalStatus) is given an alias named Status.

WHERE Clause

After you enter the columns to return in the SELECT clause, you can then define the filtering criteria in the WHERE clause. The filtering criteria are used to narrow the set of records you retrieve for a query. For example, you can click the Fields button and expand Property, and then double-click ApprovalStatus to place it under the WHERE clause. You can then select ApprovalStatus in the left pane and click the Quick Info button to display the available values for ApprovalStatus. Double-click a value to have it inserted in the left pane.

In the report shown below, the WHERE clause of ApprovalStatus = 'N' will only return the documents with an approval status of Not Approved.

If you click the Preview tab and run the report, the display will look similar to the following:

When a report is defined with parameters, the user will be prompted to fill in the parameters (input fields) prior to running the report. A parameter starts with the character @ and is part of the eQL WHERE clause. The Parameters button lists the parameters defined in the search builder area.

Note: If you specify a WHERE clause like Name = @Name, the user will have to type in the exact name for an object. If you want the user to be able to type in wildcard characters to search for an object, use Name LIKE @Name.

The Built-in Fields button lists run-time variables that may be used in the search builder area. For example, you may want to build a report that displays all documents with today as the Date Effective date. This can be accomplished using the run-time variable $Today.

ORDER BY Clause

The ORDER BY clause is used to specify the sort order and is optional. The image below is sorting by Code.